package com.verisette.ncos.service.approver.bizunit;

import java.util.List;

import org.apache.commons.lang.NumberUtils;
import org.apache.log4j.Logger;
import org.corp.agi.common.bean.PagingBean;
import org.corp.agi.common.constant.CommonConstant;
import org.corp.agi.common.exception.ProcessException;
import org.corp.agi.service.bean.ProcessContext;
import org.corp.agi.service.bean.UserBean;
import org.corp.agi.service.bizunit.CommonBusinessUnit;

import com.verisette.ncos.common.ProjectConstant;
import com.verisette.ncos.persistence.domain.CustomCustomerOrder;
import com.verisette.ncos.persistence.domain.TransCustomerOrder;

public class InquiryTransCustomerOrderBU extends CommonBusinessUnit{

	private Logger LOG = Logger.getLogger(InquiryTransCustomerOrderBU.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public void execute(ProcessContext p) throws ProcessException 
	{
		int status = ProjectConstant.STATUS_WAIT_APPROVE;
		TransCustomerOrder order = (TransCustomerOrder) p.getValueObject();
		UserBean user = p.getUserBean();
		PagingBean paging = p.getPaging();
		if(LOG.isDebugEnabled())
		{
			LOG.debug("Processing InquiryCustomerOrderBU...");
			LOG.debug(String.format("User: %1$s", user.getUsername()));
		}
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ROW_NUMBER() OVER (ORDER BY o.CREATED_DATE DESC) As Row, o.ORDER_ID, o.QUANTITY, o.STATUS, o.CREATED_DATE, o.CREATED_BY, o.APPROVED_BY, o.APPROVED_DATE, o.EXPORTED_DATE, o.EXPORTED_BY, o.RECEIVED_DATE, o.RECEIVED_BY, o.SENT_DATE, o.SENT_BY, o.UPDATED_DATE, o.UPDATED_BY, o.CREATED_USP_ID, o.APPROVED_USP_ID, o.EXPORTED_USP_ID, o.RECEIVED_USP_ID, o.SENT_USP_ID, o.UPDATED_USP_ID, o.PDF_PATH, o.NOT_APPROVED_REASON, o.APPROVED02_BY, o.APPROVED02_DATE, o.APPROVED02_USP_ID, o.PRODUCT_CODE, o.PO_PATH, o.DELIVERY_METHOD, o.DELIVERY_NAME, o.DELIVERY_ADDR, o.DELIVERY_MOBILE, o.PRINT_TYPE, o.DELIVERY_ADDR_USE, o.INVOICE_COMPANY_ID, "); 
		sb.append(" m.MMB_ID, m.MMB_TITLE_TH, m.MMB_FNAME_TH, m.MMB_MNAME_TH, m.MMB_LNAME_TH, m.MMB_COMPANY_NAME_TH, m.MMB_ADDRESS_TH, m.MMB_DEPT_TH, m.MMB_POSITION_TH, m.MMB_PHONE, m.MMB_FAX, m.MMB_EMAIL, m.MMB_TITLE_EN, m.MMB_FNAME_EN, m.MMB_MNAME_EN, m.MMB_LNAME_EN, m.MMB_COMPANY_NAME_EN, m.MMB_DEPT_EN, m.MMB_ADDRESS_EN, m.MMB_POSITION_EN, m.MMB_STATUS, m.MMB_ORDER_STATUS, m.MMB_CREATED_DATE, m.MMB_CREATED_BY, m.MMB_LAST_UPDATED_DATE, m.MMB_LAST_UPDATED_BY, m.MMB_PICTURE_PATH, m.MMB_AGENT_CODE, m.MMB_PHONE2, m.MMB_FAX2, m.MMB_EMAIL2, m.MMB_OFFICE_NAME_TH, m.MMB_OFFICE_NAME_EN, m.MMB_OFFICE_ADDR_TH, m.MMB_OFFICE_ADDR2_TH, m.MMB_OFFICE_ADDR_EN, m.MMB_OFFICE_ADDR2_EN, m.MMB_MOBILE, m.MMB_MOBILE2, ");
		sb.append(" t.MTP_ID, t.MTP_NAME, t.MTP_DESCRIPTION, t.MTP_PATH, t.MTP_CREATED_DATE, t.MTP_CREATED_BY, t.MTP_LAST_UPDATE_DATE, t.MTP_LAST_UPDATE_BY, t.MTP_IMG_REQUIRED, t.MTP_SHORT_NAME, t.MTP_PRICE_PER_UNIT, ");
		sb.append(" b.BRANCH_ID, b.BRANCH_NAME_TH, b.BRANCH_NAME_EN, b.BRANCH_ADDR_TH, b.BRANCH_ADDR_EN, b.BRANCH_ZIP_CODE, b.BRANCH_TEL_1_TH, b.PROVINCE_ID, b.BRANCH_TEL_1_EN, b.BRANCH_FAX_TH, b.BRANCH_FAX_EN, ");
		sb.append(" c.COMPANY_ID, c.COMPANY_NAME_TH, c.COMPANY_NAME_EN, c.COMPANY_ADDR_TH, c.COMPANY_ADDR_EN ");
		sb.append(" FROM TRANS_CUSTOMER_ORDER o "); 
		sb.append(" INNER JOIN MASTER_MEMBER m ON o.MMB_ID = m.MMB_ID ");
		sb.append(" INNER JOIN MASTER_TEMPLATE t ON o.MTP_ID = t.MTP_ID ");
		sb.append(" LEFT JOIN MASTER_BRANCH b ON m.BRANCH_ID = b.BRANCH_ID ");
		sb.append(" LEFT JOIN MASTER_COMPANY c ON m.COMPANY_ID = c.COMPANY_ID ");
		if(order.getStatus() != null) {
			sb.append(" WHERE o.STATUS = ? ");
		}
		
		List<TransCustomerOrder> list = null;
		if(order.getStatus() != null) {
			list = (List<TransCustomerOrder>) commonDAO.nativeQuery(sb.toString(), TransCustomerOrder.class, paging, order.getStatus());
		} else {
			list = (List<TransCustomerOrder>) commonDAO.nativeQuery(sb.toString(), TransCustomerOrder.class, paging);
		}
		
		p.setValueObject(list);
		p.setPaging(paging);
		p.setStatus(CommonConstant.SERVICE_STATUS_SUCCESS);
	}

}
